package biz.bbtec.sbm.store.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import biz.bbtec.sbm.db.dbConnection;
import biz.bbtec.sbm.store.bean.CategoryBean;
import biz.bbtec.sbm.user.bean.UserBean;

public class CategoryService {

	dbConnection db = new dbConnection();

	public boolean addcat(CategoryBean cat, UserBean user) {
		boolean flag = false;
		boolean exit = false;

		String sql1 = "select name,parentid,catid from category where orgid = "
				+ "'" + user.getOrgid() + "'";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;
		int id = 0;

		try {
			past = conn.prepareStatement(sql1);
			rs = past.executeQuery();
			while (rs.next()) {
				String title = rs.getString(1);
				int parentid = rs.getInt(2);
				if (parentid == 0 && title.equalsIgnoreCase(cat.getFirst())) {
					id = rs.getInt(3);
					exit = true;
					rs.close();
					past.close();
					break;
				}
			}
			if (exit) {
				String sql2 = "select name from category where parentid = ?";
				past = conn.prepareStatement(sql2);
				past.setInt(1, id);
				rs = past.executeQuery();
				while (rs.next()) {
					if (cat.getSecond().equalsIgnoreCase(rs.getString(1))) {
						rs.close();
						past.close();
						return false;
					}
				}
				String sql3 = "insert into category(name,parentid,orgid) values("
						+ "'"
						+ cat.getSecond()
						+ "'"
						+ ","
						+ "'"
						+ id
						+ "'"
						+ "," + "'" + user.getOrgid() + "'" + ")";
				past = conn.prepareStatement(sql3);
				past.executeUpdate();
				past.close();
				flag = true;
			} else {
				String sql4 = "insert into category(name,parentid,orgid) values("
						+ "'"
						+ cat.getFirst()
						+ "'"
						+ ","
						+ "'"
						+ 0
						+ "'"
						+ "," + "'" + user.getOrgid() + "'" + ")";
				past = conn.prepareStatement(sql4,
						Statement.RETURN_GENERATED_KEYS);
				past.executeUpdate(sql4);
				rs = past.getGeneratedKeys();
				if (rs.next()) {
					String sql5 = "insert into category(name,parentid,orgid) values("
							+ "'"
							+ cat.getSecond()
							+ "'"
							+ ","
							+ "'"
							+ rs.getInt(1)
							+ "'"
							+ ","
							+ "'"
							+ user.getOrgid()
							+ "'" + ")";
					PreparedStatement ps = conn.prepareStatement(sql5);
					ps.executeUpdate(sql5);
					ps.close();
					rs.close();
				}
				past.close();
				flag = true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public List showfirst(UserBean user){
		List<CategoryBean> list = new ArrayList<CategoryBean>();
		String sql1="select * from category where orgid=? and parentid=0";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;
		try{
			past = conn.prepareStatement(sql1);
			past.setInt(1, user.getOrgid());
			rs = past.executeQuery();
			while(rs.next()){
				CategoryBean cat = new CategoryBean();
				cat.setCatid(rs.getInt("catid"));
				cat.setFirst(rs.getString("name"));
				cat.setParentid(0);
				list.add(cat);
			}
			rs.close();
			past.close();
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeConnection(conn);
		}
		return list;
	}
	
	public List showsecond(UserBean user,int catid){
		List<CategoryBean> list = new ArrayList<CategoryBean>();
		String sql1="select * from category where orgid=? and parentid = ?";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;
		try{
			past = conn.prepareStatement(sql1);
			past.setInt(1, user.getOrgid());
			past.setInt(2, catid);
			rs = past.executeQuery();
			while(rs.next()){
				CategoryBean cat = new CategoryBean();
				cat.setCatid(rs.getInt("catid"));
				cat.setSecond(rs.getString("name"));
				cat.setParentid(0);
				list.add(cat);
			}
			rs.close();
			past.close();
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeConnection(conn);
		}
		return list;
	}
}
